﻿using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc.Controllers;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;

namespace Bridge.Shared.Extensions
{
    public class MyLogDbCommandInterceptor: DbCommandInterceptor
    {
        static readonly ConcurrentDictionary<DbCommand, DateTime> MStartTime = new ConcurrentDictionary<DbCommand, DateTime>();
        private readonly IConfiguration _configuration;
        private readonly IHttpContextAccessor _accessor;

        private ILogger<MyLogDbCommandInterceptor> _logger { get; set; }

        public MyLogDbCommandInterceptor(ILogger<MyLogDbCommandInterceptor> Logger, IConfiguration configuration)
        {
            _logger = Logger;
            _configuration = configuration;
        }
        private void OnStart(DbCommand command)
        {
            
          
            //优化SQL显示位置与格式
            string parameterizedSql = FormatCommandValue(command);
            _logger.LogInformation("开始执行SQL语句:" + parameterizedSql);
            // Debug.WriteLine("开始执行SQL语句:" + parameterizedSql);
            MStartTime.TryAdd(command, DateTime.Now);
        }
       
        public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
        {
            OnStart(command);
            return base.NonQueryExecuting(command, eventData, result);
        }
        public override ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
        {
            OnStart(command);
            return base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);
        }
        public override int NonQueryExecuted(DbCommand command, CommandExecutedEventData eventData, int result)
        {
           
            return base.NonQueryExecuted(command, eventData, result);
        }
        public override ValueTask<int> NonQueryExecutedAsync(DbCommand command, CommandExecutedEventData eventData, int result, CancellationToken cancellationToken = default)
        {
            
            return base.NonQueryExecutedAsync(command, eventData, result, cancellationToken);
        }

        public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
        {
            OnStart(command);
            return base.ScalarExecuting(command, eventData, result);
        }
        public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default)
        {
            OnStart(command);
            return base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
        }
        public override object ScalarExecuted(DbCommand command, CommandExecutedEventData eventData, object result)
        {
          
            return base.ScalarExecuted(command, eventData, result);
        }
        public override ValueTask<object> ScalarExecutedAsync(DbCommand command, CommandExecutedEventData eventData, object result, CancellationToken cancellationToken = default)
        {
        
            return base.ScalarExecutedAsync(command, eventData, result, cancellationToken);
        }


        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
        {
            OnStart(command);
            return base.ReaderExecuting(command, eventData, result);
        }
        public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
        {
            OnStart(command);
            return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
        }

        public override ValueTask<DbDataReader> ReaderExecutedAsync(DbCommand command, CommandExecutedEventData eventData, DbDataReader result, CancellationToken cancellationToken = default)
        {
            // var dicdate = SerializeDataReader(result,out DbDataReader newDataReader);
            //Log(command, dicdate);
            // return base.ReaderExecutedAsync(command, eventData, newDataReader, cancellationToken);
            
            return base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
        }
        public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result)
        {
            //var dicdate = SerializeDataReader(result, out DbDataReader newDataReader);
            //Log(command, dicdate);
            //return base.ReaderExecuted(command, eventData, newDataReader);
           
            return base.ReaderExecuted(command, eventData, result);
        }


        /// <summary>
        /// 序列化返回值并且返回一个新的DbDataReader
        /// </summary>
        protected virtual List<Dictionary<string, object>> SerializeDataReader(DbDataReader reader, out DbDataReader newDataReader)
        {
            if (reader == null)
            {
                newDataReader = null;
                return null;
            }
            var dataTable = new DataTable();
            dataTable.Load(reader);
            newDataReader = dataTable.CreateDataReader();


            return dataTable.AsEnumerable().Select(
                           row => dataTable.Columns.Cast<DataColumn>().ToDictionary(
                               column => column.ColumnName,
                               column => row[column]
                           )).ToList();
        }

        string FormatCommandValue(DbCommand command)
        {
            //优化SQL显示位置与格式
            // 获取参数化的 SQL 语句
            string parameterizedSql = command.CommandText;

            // 使用正则表达式来替换参数名
            foreach (DbParameter parameter in command.Parameters)
            {
                string parameterName = parameter.ParameterName;
                object parameterValue = parameter.Value;

                // 根据参数类型，对参数值进行适当的处理，如在 SQL 语句中添加引号等
                string formattedValue = FormatParameterValue(parameterValue);

                // 使用正则表达式来替换参数名，确保仅替换完整的参数名
                string pattern = $@"{Regex.Escape(parameterName)}\b";
                parameterizedSql = Regex.Replace(parameterizedSql, pattern, formattedValue);
            }

            return parameterizedSql;
        }

        string FormatParameterValue(object value)
        {
            if (value == null)
            {
                return "NULL";
            }
            else if (value is string || value is Guid)
            {
                return $"'{EscapeSqlString(value.ToString())}'"; // 对字符串和 GUID 类型加引号，同时防止 SQL 注入
            }
            else if (value is decimal)
            {
                return ((decimal)value).ToString("0.##########"); // 格式化 decimal 类型，保留十位小数
            }
            else if (value is DateTime)
            {
                return $"'{((DateTime)value):yyyy-MM-dd HH:mm:ss.fffffff}'"; // 格式化日期时间类型，保留七位小数
            }
            else if (value is bool)
            {
                return ((bool)value) ? "1" : "0"; // 转换布尔类型为整数
            }
            else if (value is byte[])
            {
                return "0x" + BitConverter.ToString((byte[])value).Replace("-", ""); // 二进制数据
            }
            else if (IsNumericType(value))
            {
                return value.ToString(); // 数值类型直接转换为字符串
            }
            else
            {
                return $"'{EscapeSqlString(value.ToString())}'"; // 默认情况下将值视为字符串，同时防止 SQL 注入
            }
        }

        string EscapeSqlString(string input)
        {
            // 防止 SQL 注入，将单引号替换为两个单引号
            return input.Replace("'", "''");
        }

        bool IsNumericType(object value)
        {
            // 判断是否为数值类型，可以根据需要进行扩展
            return value is sbyte || value is byte ||
                   value is short || value is ushort ||
                   value is int || value is uint ||
                   value is long || value is ulong ||
                   value is float || value is double ||
                   value is decimal;
        }
    }
}
